SQL Server: Read,Write data from Excel/CSV/Text file using T-SQL

Categories: Database; Tagged with: SQL • SQL Server • T-SQL; @ September 13th, 2012 18:28


-- Select Data from Excel file 
    SELECT *
    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=C:\DavidTemp\channel.xls', [Sheet1$])
-- Select data from CVS file:
    SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0',
    'Text;Database=C:\DavidTemp\;','SELECT * FROM [channel.csv]')
 
-- Select data From text file:
    SELECT * FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0',   
    'TEXT;Database=C:\DavidTemp\','SELECT * from channel_noData.txt')
 

 
REF:
 

Ad Hoc Querying
 

Java Mail SMTP发送邮件实例 Sending Email with SMTP using Java Mail <-> SQL Server: Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server